package com.lcifn.utils;

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Function;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class MutilDataExcelUtil {
	
    private static Logger logger = LoggerFactory.getLogger(MutilDataExcelUtil.class);
    
    static class User {
		Integer id;
		String name;
		
		public User(Integer id, String name) {
			this.id = id;
			this.name = name;
		}
		public Integer getId() {
			return id;
		}
		public void setId(Integer id) {
			this.id = id;
		}
		public String getName() {
			return name;
		}
		public void setName(String name) {
			this.name = name;
		}
		
	}
    
    public static void main(String[] args) {
    	
        List<User> userList = new ArrayList<>();
        for(int i=0; i< 50283; i++){
        	User user = new User(i, "jack"+i);
            userList.add(user);
        }
    	
    	List<ExcelBean> excelBeanList = new ArrayList<ExcelBean>();
    	excelBeanList.add(new ExcelBean("id", "主键"));
    	excelBeanList.add(new ExcelBean("name", "名称"));

		SXSSFWorkbook wb = new SXSSFWorkbook(2000);
		Sheet sheet = wb.createSheet();

		// 处理表头
		MutilDataExcelUtil.processExcelHeader(sheet, excelBeanList);

		// 每页查询记录数
		int pageSize = 2000;
		// 查询function
		Function<Integer, List<User>> dataFunc = 
				(pageNumber) -> userList.subList((pageNumber-1) * pageSize, pageNumber * pageSize);;

		// 处理数据
		MutilDataExcelUtil.processCyclicExcelData(sheet, User.class, excelBeanList, dataFunc, pageSize, 50000);

        try (FileOutputStream os = new FileOutputStream(new File("d://test.xlsx"))) {
            wb.write(os);
            wb.close();
        }catch (Exception e){
            e.printStackTrace();
        }
	}

    /**
     * 处理excel表头
     * @author lichao
     * @date 2017/12/29 16:21
     */
    public static boolean processExcelHeader(org.apache.poi.ss.usermodel.Sheet sheet, List<ExcelBean> excelBeanList) {
        try {
            sheet.setDefaultColumnWidth(20);
            Row row = sheet.createRow(0);
            // 添加标题
            for (int i = 0; i < excelBeanList.size(); i++) {
                org.apache.poi.ss.usermodel.Cell cell = row.createCell(i);
                cell.setCellValue(excelBeanList.get(i).getShowName());
            }
            return true;

        } catch (Exception ex) {
            logger.error("", ex);
            return false;
        }
    }

    /**
     * 按页循环操作数据到excel
     * @author lichao
     * @date 2017/12/30 15:39
     */
    public static <T> void processCyclicExcelData(org.apache.poi.ss.usermodel.Sheet sheet, Class<T> clazz,
                                                  List<ExcelBean> excelBeanList, Function<Integer, List<T>> queryDataFunc,
                                                  int pageSize){
        // 最大导出记录数100000
        int maxDataSize = 100000;

        processCyclicExcelData(sheet, clazz, excelBeanList,queryDataFunc, pageSize, maxDataSize);
    }

    /**
     * 按页循环操作数据到excel
     * @author lichao
     * @date 2017/12/30 15:44
     */
    public static <T> void processCyclicExcelData(org.apache.poi.ss.usermodel.Sheet sheet, Class<T> clazz,
                                                  List<ExcelBean> excelBeanList, Function<Integer, List<T>> queryDataFunc,
                                                  int pageSize, int maxDataSize){
        int pageNumber = 1;
        int startRow = 1;
        // 遍历页导出数据
        while (true) {
            List<T> dataList = queryDataFunc.apply(pageNumber++);
            if (dataList == null || dataList.size() == 0) {
                break;
            }
            // 导出数据
            processExcelData(sheet, clazz, dataList, excelBeanList, startRow);

            // 查询出数据小于pageSize或者总记录数大于100000，直接break
            int queryDataSize = dataList.size();
            startRow += queryDataSize;
            if (queryDataSize < pageSize || startRow > maxDataSize) {
                break;
            }
        }
    }

    /**
     * 处理excel数据
     * @author lichao
     * @date 2017/12/29 16:14
     */
    public static <T> boolean processExcelData(org.apache.poi.ss.usermodel.Sheet sheet, Class<T> clazz, List<T> data, List<ExcelBean> excelBeanList, int startRow) {
        logger.debug("*******导出数据开始*******");
        int cellIndex = 0;
        try {
            // 实体属性与get方法Map
            Map<String, Method> fieldReadMethodMap = getFieldReadMethodMap(clazz);
            // 填充数据
            for (int rowNum = 0; rowNum < data.size(); rowNum++) {
                Row dataRow = sheet.createRow(startRow + rowNum);
                cellIndex = 0;
                for (ExcelBean bean : excelBeanList) {
                    // 获取field名称
                    String property = bean.getProperty();
                    // 获取field对应的get方法
                    Method readMethod = fieldReadMethodMap.get(property);
                    if (readMethod == null) {
                        throw new RuntimeException("属性" + property + "在" + clazz.getName() + "类中没有getter方法");
                    }
                    readMethod.setAccessible(true);
                    // 获得field的值
                    Object value = readMethod.invoke(data.get(rowNum), new Object[] {});
                    createPoiCell(dataRow, value, cellIndex++);
                }
            }
        } catch (Exception ex) {
            logger.error("", ex);
            return false;
        }
        logger.debug("*******导出数据结束*******");
        return true;
    }

    /**
     * 处理excel输出
     * @author lichao
     * @date 2017/12/30 15:06
     */
    public static void processExcelOutput(org.apache.poi.ss.usermodel.Workbook wb, String fileName, HttpServletResponse response){
        OutputStream out = null;
        try {
            response.setContentType("application/msexcel");
            // 设定输出文件头
            response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(fileName, "utf-8")
                    + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + ".xlsx");
            out = response.getOutputStream();
            wb.write(out);
            if(wb instanceof SXSSFWorkbook){
                // dispose of temporary files backing this workbook on disk
                ((SXSSFWorkbook) wb).dispose();
            }
        } catch (Exception e) {
            logger.error("导出excel失败", e);
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    logger.error("关闭流失败！", e);
                }
            }
        }
    }

    /**
     * 创建cell
     * @author lichao
     * @date 2017/12/29 15:05
     */
    private static void createPoiCell(Row row, Object value, int cellIndex){
        org.apache.poi.ss.usermodel.Cell cell = row.createCell(cellIndex);
        if (value == null) {// value为空,显示空串
            cell.setCellValue("");
        } else {
            // 创建Cell
            if (value instanceof java.lang.Integer || value instanceof java.lang.Long) {// 数字类型
                cell.setCellValue(String.valueOf(value));
            } else if (value instanceof java.lang.Number) {// 数字类型(Decimal(15))
                cell.setCellValue(((java.lang.Number) value).doubleValue());
            } else if (value instanceof Date) {// 日期类型
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                String dateStr = sdf.format((Date) value);
                cell.setCellValue(dateStr);
            } else {// 其他
                cell.setCellValue(value.toString());
            }
        }
    }

    /**
     * 
     * @author lichao 2015年12月1日
     * @param clazz
     *            实体Class
     * @return field:getMethod Map
     * @throws IntrospectionException
     */
    private static <T> Map<String, Method> getFieldReadMethodMap(Class<T> clazz) throws IntrospectionException {
        Map<String, Method> fieldReadMethodMap = new HashMap<String, Method>();
        BeanInfo beanInfo = Introspector.getBeanInfo(clazz);
        PropertyDescriptor[] proDescrtptors = beanInfo.getPropertyDescriptors();
        if (proDescrtptors != null && proDescrtptors.length > 0) {
            for (PropertyDescriptor pd : proDescrtptors) {
                fieldReadMethodMap.put(pd.getName(), pd.getReadMethod());
            }
        }
        return fieldReadMethodMap;
    }
}
